﻿

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using MySql.Data.MySqlClient;

public class DAL_tbl_raw_material_order
{
    public DAL_tbl_raw_material_order()
    {

    }



    public List<tbl_raw_material_order> select(int RAW_ORDER_ID)
    {
        List<tbl_raw_material_order> list = new List<tbl_raw_material_order>();
        MySqlCommand cmd = new MySqlCommand();
        cmd.CommandText = "Select RAW_ORDER_ID,RM_ORDER_NUMBER,USER_CODE,CERTIFICATION_FILE,CERTIFICATION_FILE_ORIGINAL_NAME, Price, Quantity,CURRENCY_ID,CERTIFICATION_DATE,ETD,ETA,ETS,VESSEL_ID,ShippingDetails,IS_ACTIVE,CREATED_BY,CREATED_DATE,UPDATED_BY,UPDATED_DATE, PaymentTerms, ShippingTerms from tbl_raw_material_order where RAW_ORDER_ID=@RAW_ORDER_ID";
        cmd.Parameters.AddWithValue("@RAW_ORDER_ID", RAW_ORDER_ID);
        DataTable dt = DBUtil.ExecuteDataTable(cmd);
        foreach (DataRow dr in dt.Rows)
        {
            list.Add(new tbl_raw_material_order()
            {
                RAW_ORDER_ID = Convert.ToInt32(dr["RAW_ORDER_ID"]),
                RM_ORDER_NUMBER = dr["RM_ORDER_NUMBER"].ToString(),
                USER_CODE = dr["USER_CODE"].ToString(),
                Price = Util.isNull<Decimal>(dr["Price"], 0), //Convert.ToDecimal(dr["Price"].ToString()),
                QUANTITY = Util.isNull<Int32>(dr["Quantity"], 0),
                CURRENCY_ID = Convert.ToInt32(dr["CURRENCY_ID"]),
                CERTIFICATION_DATE = dr["CERTIFICATION_DATE"].toDateTimeWithoutError(),
                ETD = dr["ETD"].toDateTimeWithoutError(),
                ETA = dr["ETA"].toDateTimeWithoutError(),
                ETS = dr["ETS"].toDateTimeWithoutError(),
                VESSEL_ID = Convert.ToInt32(dr["VESSEL_ID"]),
                ShippingDetails = dr["ShippingDetails"].ToString(),
                PaymentTerms = dr["PaymentTerms"].ToString(),
                ShippingTerms = dr["ShippingTerms"].ToString(),
                IS_ACTIVE = Convert.ToBoolean(dr["IS_ACTIVE"]),
                CREATED_BY = dr["CREATED_BY"].ToString(),
                CREATED_DATE = dr["CREATED_DATE"].toDateTimeWithoutError(),
                UPDATED_BY = dr["UPDATED_BY"].ToString(),
                UPDATED_DATE = dr["UPDATED_DATE"].toDateTimeWithoutError()
            });
        }

        return list;
    }



    public List<tbl_raw_material_order> selectAll()
    {
        List<tbl_raw_material_order> list = new List<tbl_raw_material_order>();
        MySqlCommand cmd = new MySqlCommand();
        cmd.CommandText = "Select RAW_ORDER_ID,RM_ORDER_NUMBER,USER_CODE,CERTIFICATION_FILE,CERTIFICATION_FILE_ORIGINAL_NAME, Price, Quantity,CURRENCY_ID,CERTIFICATION_DATE,ETD,ETA,ETS,VESSEL_ID, ShippingDetails,IS_ACTIVE,CREATED_BY,CREATED_DATE,UPDATED_BY,UPDATED_DATE, PaymentTerms, ShippingTerms from tbl_raw_material_order Order By RAW_ORDER_ID";

        DataTable dt = DBUtil.ExecuteDataTable(cmd);
        foreach (DataRow dr in dt.Rows)
        {
            list.Add(new tbl_raw_material_order()
            {
                RAW_ORDER_ID = Convert.ToInt32(dr["RAW_ORDER_ID"]),
                RM_ORDER_NUMBER = dr["RM_ORDER_NUMBER"].ToString(),
                USER_CODE = dr["USER_CODE"].ToString(),
                Price = Util.isNull<Decimal>(dr["Price"], 0), //Convert.ToDecimal(dr["Price"].ToString()),
                QUANTITY = Util.isNull<Int32>(dr["Quantity"], 0),
                CURRENCY_ID = Convert.ToInt32(dr["CURRENCY_ID"]),
                CERTIFICATION_DATE = dr["CERTIFICATION_DATE"].toDateTimeWithoutError(),
                ETD = dr["ETD"].toDateTimeWithoutError(),
                ETA = dr["ETA"].toDateTimeWithoutError(),
                ETS = dr["ETS"].toDateTimeWithoutError(),
                VESSEL_ID = Convert.ToInt32(dr["VESSEL_ID"]),
                ShippingDetails = dr["ShippingDetails"].ToString(),
                PaymentTerms = dr["PaymentTerms"].ToString(),
                ShippingTerms = dr["ShippingTerms"].ToString(),
                IS_ACTIVE = Convert.ToBoolean(dr["IS_ACTIVE"]),
                CREATED_BY = dr["CREATED_BY"].ToString(),
                CREATED_DATE = dr["CREATED_DATE"].toDateTimeWithoutError(),
                UPDATED_BY = dr["UPDATED_BY"].ToString(),
                UPDATED_DATE = dr["UPDATED_DATE"].toDateTimeWithoutError()
            });
        }

        return list;
    }


    public DataTable selectAllForGrid()
    {
        MySqlCommand cmd = new MySqlCommand();
        cmd.CommandText = @"Select RAW_ORDER_ID,RM_ORDER_NUMBER as 'RMOrderNumber',sm.SUPPLIER_NAME as 'SupplierName',sm.CERTIFICATION_FILE_ORIGINAL_NAME as 'Certificate', Price, Quantity,cm.CURRENCY_NAME as 'Currency',
                            sm.CERTIFICATION_DATE as 'CertificateDate',ETD,ETA,ETS,vm.VESSEL_TYPE as 'ShippingType', ShippingDetails as 'ShippingVessels', rmo.PaymentTerms as 'Payment Terms' , rmo.ShippingTerms as 'Shipping Terms',rmo.IS_ACTIVE as 'Active',rmo.CREATED_BY,rmo.CREATED_DATE,rmo.UPDATED_BY,rmo.UPDATED_DATE
                            from tbl_raw_material_order rmo left outer join tbl_supplier_master sm on rmo.USER_CODE=sm.USER_CODE
                            left outer join tbl_currency_master cm on rmo.CURRENCY_ID=cm.CURRENCY_ID
                            left outer join tbl_vessel_master vm on rmo.VESSEL_ID=vm.VESSEL_ID
                            Order By RAW_ORDER_ID desc";

        DataTable dt = DBUtil.ExecuteDataTable(cmd);
        return dt;
    }


    public List<tbl_raw_material_order> ddlRM_Order_Number()
    {
        List<tbl_raw_material_order> list = new List<tbl_raw_material_order>();
        MySqlCommand cmd = new MySqlCommand();
        cmd.CommandText = "Select distinct RM_ORDER_NUMBER from tbl_raw_material_order Order By RM_ORDER_NUMBER desc";

        DataTable dt = DBUtil.ExecuteDataTable(cmd);
        foreach (DataRow dr in dt.Rows)
        {
            list.Add(new tbl_raw_material_order()
            {
                RM_ORDER_NUMBER = dr["RM_ORDER_NUMBER"].ToString()
            });
        }

        return list;
    }



    public int insert(String RM_ORDER_NUMBER, String USER_CODE, String CERTIFICATION_FILE, String CERTIFICATION_FILE_ORIGINAL_NAME, Decimal Price, int QUANTITY, int CURRENCY_ID, DateTime CERTIFICATION_DATE, DateTime ETD, DateTime ETA, DateTime ETS, int VESSEL_ID, string ShippingDetails, bool IS_ACTIVE, String CREATED_BY, DateTime CREATED_DATE, String PaymentTerms, String ShippingTerms)
    {
        int i = 0;
        string sql = @"Insert into tbl_raw_material_order(RM_ORDER_NUMBER, USER_CODE, CERTIFICATION_FILE, CERTIFICATION_FILE_ORIGINAL_NAME, Price, Quantity, CURRENCY_ID, CERTIFICATION_DATE, ETD, ETA, ETS, VESSEL_ID, ShippingDetails, IS_ACTIVE, CREATED_BY, CREATED_DATE, PaymentTerms, ShippingTerms)
                        values(@RM_ORDER_NUMBER, @USER_CODE, @CERTIFICATION_FILE, @CERTIFICATION_FILE_ORIGINAL_NAME, @Price, @QUANTITY, @CURRENCY_ID, @CERTIFICATION_DATE, @ETD, @ETA, @ETS, @VESSEL_ID, @ShippingDetails, @IS_ACTIVE, @CREATED_BY, @CREATED_DATE, @PaymentTerms, @ShippingTerms)";

        MySqlCommand cmd = new MySqlCommand(sql);

        cmd.Parameters.AddWithValue("@RM_ORDER_NUMBER", RM_ORDER_NUMBER);
        cmd.Parameters.AddWithValue("@USER_CODE", USER_CODE);
        cmd.Parameters.AddWithValue("@CERTIFICATION_FILE", CERTIFICATION_FILE);
        cmd.Parameters.AddWithValue("@CERTIFICATION_FILE_ORIGINAL_NAME", CERTIFICATION_FILE_ORIGINAL_NAME);
        cmd.Parameters.AddWithValue("@Price", Price);
        cmd.Parameters.AddWithValue("@QUANTITY", QUANTITY);
        cmd.Parameters.AddWithValue("@CURRENCY_ID", CURRENCY_ID);
        cmd.Parameters.AddWithValue("@CERTIFICATION_DATE", CERTIFICATION_DATE);
        cmd.Parameters.AddWithValue("@ETD", ETD);
        cmd.Parameters.AddWithValue("@ETA", ETA);
        cmd.Parameters.AddWithValue("@ETS", ETS);
        cmd.Parameters.AddWithValue("@VESSEL_ID", VESSEL_ID);
        cmd.Parameters.AddWithValue("@ShippingDetails", ShippingDetails);

        cmd.Parameters.AddWithValue("@PaymentTerms", PaymentTerms);
        cmd.Parameters.AddWithValue("@ShippingTerms", ShippingTerms);

        cmd.Parameters.AddWithValue("@IS_ACTIVE", IS_ACTIVE);
        cmd.Parameters.AddWithValue("@CREATED_BY", CREATED_BY);
        cmd.Parameters.AddWithValue("@CREATED_DATE", CREATED_DATE);

        i = DBUtil.ExecuteNonQuery(cmd);
        return i;
    }



    public int update(int RAW_ORDER_ID, String RM_ORDER_NUMBER, String USER_CODE, String CERTIFICATION_FILE, String CERTIFICATION_FILE_ORIGINAL_NAME, Decimal Price, int QUANTITY, int CURRENCY_ID, DateTime CERTIFICATION_DATE, DateTime ETD, DateTime ETA, DateTime ETS, int VESSEL_ID, string ShippingDetails, bool IS_ACTIVE, String UPDATED_BY, DateTime UPDATED_DATE, String PaymentTerms, String ShippingTerms)
    {
        int i = 0;
        string sql = @"Update tbl_raw_material_order SET RM_ORDER_NUMBER=@RM_ORDER_NUMBER, USER_CODE=@USER_CODE, CERTIFICATION_FILE=@CERTIFICATION_FILE, CERTIFICATION_FILE_ORIGINAL_NAME=@CERTIFICATION_FILE_ORIGINAL_NAME, Price=@Price, Quantity=@Quantity, CURRENCY_ID=@CURRENCY_ID, CERTIFICATION_DATE=@CERTIFICATION_DATE, ETD=@ETD, ETA=@ETA, ETS=@ETS, VESSEL_ID=@VESSEL_ID, ShippingDetails=@ShippingDetails, IS_ACTIVE=@IS_ACTIVE, UPDATED_BY=@UPDATED_BY, UPDATED_DATE=@UPDATED_DATE, PaymentTerms=@PaymentTerms, ShippingTerms=@ShippingTerms WHERE RAW_ORDER_ID=@RAW_ORDER_ID";

        MySqlCommand cmd = new MySqlCommand(sql);

        cmd.Parameters.AddWithValue("@RAW_ORDER_ID", RAW_ORDER_ID);
        cmd.Parameters.AddWithValue("@RM_ORDER_NUMBER", RM_ORDER_NUMBER);
        cmd.Parameters.AddWithValue("@USER_CODE", USER_CODE);
        cmd.Parameters.AddWithValue("@CERTIFICATION_FILE", CERTIFICATION_FILE);
        cmd.Parameters.AddWithValue("@CERTIFICATION_FILE_ORIGINAL_NAME", CERTIFICATION_FILE_ORIGINAL_NAME);
        cmd.Parameters.AddWithValue("@Price", Price);
        cmd.Parameters.AddWithValue("@QUANTITY", QUANTITY);
        cmd.Parameters.AddWithValue("@CURRENCY_ID", CURRENCY_ID);
        cmd.Parameters.AddWithValue("@CERTIFICATION_DATE", CERTIFICATION_DATE);
        cmd.Parameters.AddWithValue("@ETD", ETD);
        cmd.Parameters.AddWithValue("@ETA", ETA);
        cmd.Parameters.AddWithValue("@ETS", ETS);
        cmd.Parameters.AddWithValue("@VESSEL_ID", VESSEL_ID);
        cmd.Parameters.AddWithValue("@ShippingDetails", ShippingDetails);

        cmd.Parameters.AddWithValue("@PaymentTerms", PaymentTerms);
        cmd.Parameters.AddWithValue("@ShippingTerms", ShippingTerms);

        cmd.Parameters.AddWithValue("@IS_ACTIVE", IS_ACTIVE);
        cmd.Parameters.AddWithValue("@UPDATED_BY", UPDATED_BY);
        cmd.Parameters.AddWithValue("@UPDATED_DATE", UPDATED_DATE);

        i = DBUtil.ExecuteNonQuery(cmd);
        return i;
    }



    public int delete(int RAW_ORDER_ID)
    {
        int i = 0;
        string sql = "Delete FROM tbl_raw_material_order WHERE RAW_ORDER_ID=@RAW_ORDER_ID";

        MySqlCommand cmd = new MySqlCommand(sql);

        cmd.Parameters.AddWithValue("@RAW_ORDER_ID", RAW_ORDER_ID);

        i = DBUtil.ExecuteNonQuery(cmd);
        return i;
    }


    public DataTable getQtyForRMOrder()
    {


        string cmd = "Select RAW_ORDER_ID,RM_ORDER_NUMBER,USER_CODE,CERTIFICATION_FILE,CERTIFICATION_FILE_ORIGINAL_NAME,CURRENCY_ID,CERTIFICATION_DATE,QUANTITY,ETD,ETA,ETS,VESSEL_ID,IS_ACTIVE,CREATED_BY,CREATED_DATE,UPDATED_BY,UPDATED_DATE from tbl_raw_material_order where IS_ACTIVE='1'";

        try
        {
            return DBUtil.ExecuteDataTable(cmd);
        }
        finally
        {

            cmd = null;
        }
    }



}